#include "productdao.h"
#include <QDebug>
#include <QSqlQuery>
#include <QSqlError>

// 产品数据访问对象(DAO)，负责产品相关的数据库操作
// 封装了产品的增删改查等数据库交互逻辑，依赖Database对象进行实际数据库操作
ProductDAO::ProductDAO(Database *db) : m_db(db) {}

// 检查产品是否存在
// 通过产品名称查询数据库，判断该名称的产品是否已存在
bool ProductDAO::isProductExist(const QString &product_name)
{
    QString query = "SELECT product_id FROM product WHERE product_name = ?";
    QVector<QMap<QString, QVariant>> result = m_db->query(query, {product_name});
    return !result.isEmpty();
}

// 添加新产品
// 向product表插入一条新记录，参数对应产品的各项属性
// 返回值表示插入操作是否成功（1/0对应bool的true/false）
bool ProductDAO::addProduct(const QString &product_name, const QString &specification,
                           const QString &color, const QString &supplier,
                           double purchase_price, double selling_price,
                           int category_id, bool allow_member_discount)
{
    QString query = "INSERT INTO product (product_name, specification, color, supplier, "
                   "purchase_price, selling_price, category_id, allow_member_discount) "
                   "VALUES (?, ?, ?, ?, ?, ?, ?, ?)";

    return m_db->execute(query, {
        product_name,
        specification,
        color,
        supplier,
        purchase_price,
        selling_price,
        category_id,
        allow_member_discount ? 1 : 0  // 布尔值转换为数据库存储的1/0
    });
}

// 获取所有产品
// 查询product表所有记录，按产品名称排序后返回
QVector<QMap<QString, QVariant>> ProductDAO::getAllProducts()
{
    // 联合查询product表和inventory表获取完整商品信息包括库存
    QString query = "SELECT p.*, i.current_stock as stock_quantity, i.safety_stock, "
                   "i.last_restock_date, i.last_sale_date "
                   "FROM product p "
                   "LEFT JOIN inventory i ON p.product_id = i.product_id "
                   "ORDER BY p.product_id";
    QVector<QMap<QString, QVariant>> products = m_db->query(query);
    
    // 为没有库存记录的商品设置默认库存值
    for (auto& product : products) {
        if (!product.contains("stock_quantity") || product["stock_quantity"].isNull()) {
            product["stock_quantity"] = 0;
        }
    }
    
    return products;
}

// 根据ID获取产品详情
// 按产品ID查询单条记录，返回包含产品所有属性的映射；无结果则返回空映射
QMap<QString, QVariant> ProductDAO::getProductById(int product_id)
{
    // 联合查询product表和inventory表获取完整商品信息
    QString query = "SELECT p.*, i.current_stock as stock_quantity, i.safety_stock, "
                   "i.last_restock_date, i.last_sale_date "
                   "FROM product p "
                   "LEFT JOIN inventory i ON p.product_id = i.product_id "
                   "WHERE p.product_id = ?";
    QVector<QMap<QString, QVariant>> result = m_db->query(query, {product_id});

    if (!result.isEmpty()) {
        QMap<QString, QVariant> product = result[0];
        // 如果没有库存记录，设置默认值
        if (!product.contains("stock_quantity") || product["stock_quantity"].isNull()) {
            product["stock_quantity"] = 0;
        }
        return product;
    }
    return QMap<QString, QVariant>();
}

// 更新产品信息
// 根据产品ID更新对应记录的各项属性，返回更新操作是否成功
bool ProductDAO::updateProduct(int product_id, const QString &product_name,
                              const QString &specification, const QString &color,
                              const QString &supplier, double purchase_price,
                              double selling_price, int category_id,
                              bool allow_member_discount)
{
    QString query = "UPDATE product SET product_name = ?, specification = ?, color = ?, "
                   "supplier = ?, purchase_price = ?, selling_price = ?, "
                   "category_id = ?, allow_member_discount = ? WHERE product_id = ?";

    return m_db->execute(query, {
        product_name,
        specification,
        color,
        supplier,
        purchase_price,
        selling_price,
        category_id,
        allow_member_discount ? 1 : 0,
        product_id  // WHERE条件，指定更新的产品ID
    });
}

// 删除产品
// 根据产品ID删除对应记录，返回删除操作是否成功
bool ProductDAO::deleteProduct(int product_id)
{
    QString query = "DELETE FROM product WHERE product_id = ?";
    return m_db->execute(query, {product_id});
}

// 按名称搜索产品
// 对产品名称进行模糊查询（包含关键词），返回匹配的产品列表并按名称排序
QVector<QMap<QString, QVariant>> ProductDAO::searchProductsByName(const QString &name)
{
    // 联合查询product表和inventory表获取完整商品信息包括库存
    QString query = "SELECT p.*, i.current_stock as stock_quantity, i.safety_stock, "
                   "i.last_restock_date, i.last_sale_date "
                   "FROM product p "
                   "LEFT JOIN inventory i ON p.product_id = i.product_id "
                   "WHERE p.product_name LIKE ? ORDER BY p.product_name";
    QString searchPattern = "%" + name + "%";  // 构建模糊查询模式（前后加%）
    QVector<QMap<QString, QVariant>> products = m_db->query(query, {searchPattern});
    
    // 为没有库存记录的商品设置默认库存值
    for (auto& product : products) {
        if (!product.contains("stock_quantity") || product["stock_quantity"].isNull()) {
            product["stock_quantity"] = 0;
        }
    }
    
    return products;
}

bool ProductDAO::updateProductField(int productId, const QString &fieldName, const QVariant &value)
{
    QString query = QString("UPDATE product SET %1 = ? WHERE product_id = ?").arg(fieldName);
    return m_db->execute(query, {value, productId});
}


QVector<QMap<QString, QVariant>> ProductDAO::getProductsByCategory(int category_id)
{
    // 联合查询product表和inventory表获取完整商品信息包括库存
    QString query = "SELECT p.*, i.current_stock as stock_quantity, i.safety_stock, "
                   "i.last_restock_date, i.last_sale_date "
                   "FROM product p "
                   "LEFT JOIN inventory i ON p.product_id = i.product_id "
                   "WHERE p.category_id = ? ORDER BY p.product_id";
    QVector<QMap<QString, QVariant>> products = m_db->query(query, {category_id});
    
    // 为没有库存记录的商品设置默认库存值
    for (auto& product : products) {
        if (!product.contains("stock_quantity") || product["stock_quantity"].isNull()) {
            product["stock_quantity"] = 0;
        }
    }
    
    return products;
}

QVector<QMap<QString, QVariant>> ProductDAO::getAllCategories()
{
    QString query = "SELECT category_id, category_name FROM category ORDER BY category_id";
    return m_db->query(query);
}


QMap<int, int> ProductDAO::getCategoryStatistics()
{
    QMap<int, int> categoryStats;

    // 查询每个分类的商品数量
    QString query = "SELECT category_id, COUNT(*) as count FROM product GROUP BY category_id";
    QVector<QMap<QString, QVariant>> result = m_db->query(query);

    for (const auto &row : result) {
        int categoryId = row["category_id"].toInt();
        int count = row["count"].toInt();
        categoryStats[categoryId] = count;
    }

    return categoryStats;
}

QVector<QMap<QString, QVariant>> ProductDAO::searchProductsByCode(const QString& code)
{
    QString query = "SELECT * FROM product WHERE product_id LIKE ? ORDER BY product_id";
    QString searchPattern = code + "%";  // 前缀匹配
    return m_db->query(query, {searchPattern});
}
